package org.samples.dao.imp;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;

import org.hibernate.transform.Transformers;
import org.samples.dao.IExamScoreDAO;
import org.samples.model.ExamScore;
import org.samples.model.PagerModel;
import org.samples.model.vo.ExamScoreVO;
import org.samples.support.SystemContext;
import org.springframework.stereotype.Repository;

@Repository
public class ExamScoreDAOImp extends BaseHibernateDAO<ExamScore> implements IExamScoreDAO {

	@Override
	public ExamScore getScore(String userId, Integer subId) {
		String hql = " FROM ExamScore es WHERE es.userId = :userId AND es.subjectId = :subjectId ";
		return (ExamScore) this.createQuery(hql)
				.setString("userId", userId)
				.setInteger("subjectId", subId)
				.uniqueResult();
	}

	@Override
	public ExamScoreVO getScoreById(Integer id) {
		StringBuilder hql = new StringBuilder();
		hql.append("SELECT user.userName AS userName, sub.name AS subjectName, es.examTime AS examTime, es.score AS score ");
		hql.append("FROM ExamScore es, ExamUser user, ExamSubject sub ");
		hql.append("WHERE es.userId = user.userId ");
		hql.append("AND es.subjectId = sub.subId ");
		hql.append("AND es.id = :id ");
		return (ExamScoreVO) this.createQuery(hql.toString())
				.setResultTransformer(Transformers.aliasToBean(ExamScoreVO.class))
				.setInteger("id", id)
				.uniqueResult();
	}

	@SuppressWarnings("unchecked")
	@Override
	public PagerModel<ExamScoreVO> getAllScore(Map<String, String> conditionMap) {
		StringBuilder hql = new StringBuilder();
		StringBuilder count = new StringBuilder();
		StringBuilder condition = new StringBuilder();
		
		hql.append("SELECT es.id AS id, user.userId AS userId, user.userName AS userName, sub.subId AS subjectId, sub.name AS subjectName, es.examTime AS examTime, es.score AS score ");
		hql.append("FROM ExamScore es, ExamUser user, ExamSubject sub ");
		hql.append("WHERE es.userId = user.userId ");
		hql.append("AND es.subjectId = sub.subId ");
		
		count.append("SELECT count(*) ");
		count.append("FROM ExamScore es, ExamUser user, ExamSubject sub ");
		count.append("WHERE es.userId = user.userId ");
		count.append("AND es.subjectId = sub.subId ");
		
		if(conditionMap != null){
			Iterator<Entry<String, String>> it = conditionMap.entrySet().iterator();
			while(it.hasNext()){
				Entry<String, String> currentCondition = it.next();
				if("subjectName".equals(currentCondition.getKey())){
					condition.append("AND sub.name LIKE '%" + currentCondition.getValue() +"%' ");
				}else if("userId".equals(currentCondition.getKey())){
					condition.append("AND user.userId LIKE '%" + currentCondition.getValue() +"%' ");
				}else if("userName".equals(currentCondition.getKey())){
					condition.append("AND user.userName LIKE '%" + currentCondition.getValue() +"%' ");
				}else if("examTime".equals(currentCondition.getKey())){
					if(currentCondition.getValue() != null){
						condition.append("AND DATE_FORMAT(es.examTime, '%Y-%m-%d %H:%i:%s') LIKE '%" + currentCondition.getValue() +"%' ");
					}
				}
			}
		}
		
		//分页
		PagerModel<ExamScoreVO> page = new PagerModel<ExamScoreVO>();
		ArrayList<ExamScoreVO> dates = new ArrayList<ExamScoreVO>();
		int offset = SystemContext.getOffset();
		int pageSize = SystemContext.getSize();
		//使用hibernate完成分页
		dates = (ArrayList<ExamScoreVO>) this.getSession().createQuery(hql.toString() + condition.toString()).setFirstResult(offset).setMaxResults(pageSize).setResultTransformer(Transformers.aliasToBean(ExamScoreVO.class)).list();
		page.setDates(dates);
		page.setOffset(offset);
		page.setPageSize(pageSize);
		//获取总记录数
		long totalSize = (long) this.getSession().createQuery(count.toString() + condition.toString()).uniqueResult();
		page.setTotalSize(totalSize);
		return page;
	}

}
